package com.gzy.studentsociety.dao;

import com.gzy.studentsociety.entity.Society;
import com.gzy.studentsociety.entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @version: java version 1.8
 * @Author: Chuan Liu
 * @description:
 * @date: 2024-10-31 21:06
 */
public class SocietyDaoImpl extends BaseDao implements SocietyDao {
    public Integer insertSociety(Society society) throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "insert into society value (null,?,?,?,?,?)";
        return qr.update(sql,society.getSocietyCreator(), society.getSocietyCreateDate(),
                society.getSocietyName(), society.getSocietyIntro(),society.getSocietyStatus());
    }

    public List<Society> getAllSociety() throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "select societyId, society_creator as societyCreator, society_create_date as societyCreateDate, " +
                "society_name as societyName, society_intro as societyIntro, " +
                "society_status as societyStatus from society";

        return qr.query(sql, new BeanListHandler<Society>(Society.class));
    }

    public static void main(String[] args) {
        SocietyDaoImpl dao = new SocietyDaoImpl();
        List<Society> societies = null;
        try {
            societies = dao.getAllSociety();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(societies);
    }

    @Override
    public Integer updateSociety(Society society) throws SQLException {
        StringBuffer buffer = new StringBuffer("update society set  ");
        QueryRunner qr = new QueryRunner(getDataSource());
        int result = 0;
        if(society.getSocietyName() != null){
            buffer.append(" society_name = ? ").append("where societyId = ?");
            result = qr.update(buffer.toString(),society.getSocietyName(),society.getSocietyId());
        }else if (society.getSocietyIntro() != null){
            buffer.append(" society_intro = ? ").append("where societyId = ?");
            result = qr.update(buffer.toString(),society.getSocietyIntro(),society.getSocietyId());
        }else if (society.getSocietyStatus() != null){
            buffer.append(" society_status = ? ").append("where societyId = ?");
            result = qr.update(buffer.toString(),society.getSocietyStatus(),society.getSocietyId());
        }
        return result;
    }

    @Override
    public Society queryByStudentNumber(String number) throws SQLException {
        String sql = "select a.* from society a,student,member where studentNumber =" +
                " ? and student.studentId = member.memberStudentId and member.member_societyId = a.societyId";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql,new BeanHandler<Society>(Society.class),number);
    }

    @Override
    public List<Student> queryStudent(String name, String number) throws SQLException {
        StringBuffer buffer = new StringBuffer("select * from student where 1 = 1" );
        QueryRunner qr = new QueryRunner(getDataSource());
        if (name != null && name !=""){
            buffer.append("and studentName like concat('%',?,'%')");
        }else if (number != null && number !=""){
            buffer.append("and studentNumber like concat('%',?,'%')");
        }
        List<Student> students = null;
        if (name != null && name !="" && (number == null || number == "")){
            students = qr.query(buffer.toString(),new BeanListHandler<Student>(Student.class),name);
        }else if (number != null && number != "" && (name == null || name == "")){
            students = qr.query(buffer.toString(),new BeanListHandler<Student>(Student.class),number);
        }else if (name != null && name != "" && (number != null && number != "")){
            students = qr.query(buffer.toString(),new BeanListHandler<Student>(Student.class),name,number);
        }else {
            students = qr.query(buffer.toString(),new BeanListHandler<Student>(Student.class));
        }
        return students;
    }


}
